﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    /// <summary>
    /// 级别数据层
    /// </summary>
    public class LevelInfoDal : SqlServerHelper, IDisposable
    {
        public List<E_LevelInfo> GetLevelInfoList(string strWhere)
        {
            List<E_LevelInfo> list = new List<E_LevelInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT");
            strSql.Append(" Id");
            strSql.Append(",LevelName");
            strSql.Append(",Status");
            strSql.AppendFormat(" FROM E_LevelInfo WHERE 1=1 {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int Id = r.GetOrdinal("Id");
                int LevelName = r.GetOrdinal("LevelName");
                int Status = r.GetOrdinal("Status");
                E_LevelInfo model = null;
                while (r.Read())
                {
                    model = new E_LevelInfo();
                    model.Id = r.GetInt32(Id);
                    model.LevelName = r.GetString(LevelName);
                    model.Status = r.GetInt32(Status);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public int AddLevelInfo(E_LevelInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO E_LevelInfo");
            strSql.Append("(");
            strSql.Append("LevelName,");
            strSql.Append("Status,");
            strSql.Append("ParentID");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@LevelName,");
            strSql.Append("@Status,");
            strSql.Append("@ParentID");
            strSql.Append(");SELECT @@IDENTITY;");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@LevelName", SqlDbType.NVarChar, 60) { Value = model.LevelName });
            pList.Add(new SqlParameter("@Status", SqlDbType.Int, 4) { Value = model.Status });
            pList.Add(new SqlParameter("@ParentID", SqlDbType.Int, 4) { Value = model.ParentID });
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()).ToString());
        }

        public bool DelLevelInfo(int Id)
        {
            string strSql = @"
            DELETE FROM dbo.E_LevelInfo WHERE Id=@Id;
            DELETE FROM dbo.E_DepLevelInfo WHERE LevelId=@Id;";
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@Id", SqlDbType.Int, 4) { Value = Id });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool EditLevelInfo(E_LevelInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE E_LevelInfo SET ");
            strSql.Append(" LevelName=@LevelName,");
            strSql.Append(" ParentID=@ParentID");
            strSql.Append(" WHERE Id=@Id");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@Id", SqlDbType.Int, 4) { Value = model.Id });
            pList.Add(new SqlParameter("@LevelName", SqlDbType.NVarChar, 60) { Value = model.LevelName });
            pList.Add(new SqlParameter("@ParentID", SqlDbType.Int, 4) { Value = model.ParentID });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public List<E_LevelInfo> GetLevelInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<E_LevelInfo> list = new List<E_LevelInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS ORDERBYID");
            strSql.Append(",Id");
            strSql.Append(",LevelName");
            strSql.Append(",Status");
            strSql.Append(",ParentID");
            strSql.AppendFormat(" FROM E_LevelInfo WHERE 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int Id = r.GetOrdinal("Id");
                int LevelName = r.GetOrdinal("LevelName");
                int Status = r.GetOrdinal("Status");
                int ParentID = r.GetOrdinal("ParentID");
                E_LevelInfo model = null;
                while (r.Read())
                {
                    model = new E_LevelInfo
                    {
                        Id = r.GetInt32(Id),
                        LevelName = r.GetString(LevelName),
                        Status = r.GetInt32(Status),
                        ParentID = r.GetInt32(ParentID)
                    };
                    list.Add(model);
                }
                r.Close();
            }
            totalCount = GetLevelInfoCount(strWhere);
            return list;
        }

        public int GetLevelInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM E_LevelInfo WHERE [Status]=0  {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public E_LevelInfo GetLevelInfoModel(int id)
        {
            E_LevelInfo model = new E_LevelInfo();
            string strSql = @"
SELECT dbo.E_DepLevelInfo.Id,DepId,LevelName,ParentID FROM E_LevelInfo  WITH(NOLOCK)
INNER JOIN E_DepLevelInfo WITH(NOLOCK)ON dbo.E_LevelInfo.Id = dbo.E_DepLevelInfo.LevelId
WHERE dbo.E_LevelInfo.Id=@Id";
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@Id", SqlDbType.Int, 4) { Value = id }
            };
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int Id = r.GetOrdinal("Id");
                int LevelName = r.GetOrdinal("LevelName");
                int ParentID = r.GetOrdinal("ParentID");
                int DepId = r.GetOrdinal("DepId");
                if (r.Read())
                {
                    model.Id = r.GetInt32(Id);
                    model.LevelName = r.GetString(LevelName);
                    model.ParentID = r.GetInt32(ParentID);
                    model.DepId = r.GetInt32(DepId);
                }
                r.Close();
            }
            return model;
        }
    }
}
